//
//  DBFrameService.m
//  iphone.mobileoa
//
//  Created by 李亚春 on 13-5-23.
//  Copyright (c) 2013年 Epoint. All rights reserved.
//

#import "DBFrameService.h"
#import "EpointModelOu.h"
#import "EpointCateGoryInfo.h"
#import "EpointCacheDB.h"
#import "ContactModel.h"
#import "EncryptUtil.h"

@implementation DBFrameService

+(NSMutableArray *)getContactsListByParentGuid:(NSString *)_parentguid
{
    sqlite3 *framedb = [EpointFrameDB getFrameDB];
    //获取部门
    NSString *ousql = [NSString stringWithFormat:@"select ouname,ouguid from ou where parentguid = '%@'",_parentguid];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [ousql UTF8String], -1, &statement, nil);
    NSMutableArray *dataArray = [[NSMutableArray alloc]init];
    
    while (sqlite3_step(statement)==SQLITE_ROW)
    {
        NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];
        NSString *ouname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        NSString *ouguid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        [dic setObject:ouname forKey:@"name"];
        [dic setObject:ouguid forKey:@"guid"];
        [dic setObject:@"ou" forKey:@"type"];
        [dataArray addObject:dic];
    }
    
    //人员
    NSString *usersql = [NSString stringWithFormat:@"select displayname,userguid,title,ouname,user.mobile from user,ou where user.ouguid = ou.ouguid and user.ouguid = '%@'",_parentguid];
    sqlite3_prepare_v2(framedb, [usersql UTF8String], -1, &statement, nil);
    while (sqlite3_step(statement)==SQLITE_ROW) {
        NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];
        NSString *displayname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        NSString *userguid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        NSString *ouname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        NSString *title = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        NSString *mobile = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        [dic setObject:displayname forKey:@"name"];
        [dic setObject:mobile forKey:@"mobile"];
        [dic setObject:userguid forKey:@"guid"];
        [dic setObject:@"user" forKey:@"type"];
        [dic setObject:ouname forKey:@"ouname"];
        [dic setObject:title forKey:@"title"];
        [dataArray addObject:dic];
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return dataArray;
}

+(int)getUserCount
{
    sqlite3 *framedb = [EpointFrameDB getFrameDB];
    NSString *selectSql=[NSString stringWithFormat:@"select count(*) from user"];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    int count;
    while (sqlite3_step(statement)==SQLITE_ROW) {
        count = sqlite3_column_int(statement, 0);
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return count;
}

+(int)getOuCount
{
    sqlite3 *framedb = [EpointFrameDB getFrameDB];
    NSString *selectSql=[NSString stringWithFormat:@"select count(*) from ou"];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    int count;
    while (sqlite3_step(statement)==SQLITE_ROW) {
        count = sqlite3_column_int(statement, 0);
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return count;
}

+(NSMutableArray *)getContactsListByKeyWord:(NSString *)_keyword
{
    sqlite3 *framedb = [EpointFrameDB getFrameDB];
    //获取部门
    NSString *ousql = [NSString stringWithFormat:@"select ouname,ouguid from ou where ouname like '%%%@%%' or namepyshou like '%%%@%%'",_keyword,_keyword];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [ousql UTF8String], -1, &statement, nil);
    NSMutableArray *dataArray = [[NSMutableArray alloc]init];
    
    while (sqlite3_step(statement)==SQLITE_ROW)
    {
        NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];
        NSString *ouname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        NSString *ouguid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        [dic setObject:ouname forKey:@"name"];
        [dic setObject:ouguid forKey:@"guid"];
        [dic setObject:@"ou" forKey:@"type"];
        [dataArray addObject:dic];
    }
    
    //人员
    NSString *usersql = [NSString stringWithFormat:@"select displayname,userguid,title,ouname from user,ou where LOWER(user.ouguid) = LOWER(ou.ouguid) and (user.displayname like '%%%@%%' or user.namepyshou like '%%%@%%')",_keyword,_keyword];
    sqlite3_prepare_v2(framedb, [usersql UTF8String], -1, &statement, nil);
    while (sqlite3_step(statement)==SQLITE_ROW) {
        NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];
        NSString *displayname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        NSString *userguid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        NSString *ouname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        NSString *title = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        [dic setObject:displayname forKey:@"name"];
        [dic setObject:userguid forKey:@"guid"];
        [dic setObject:@"user" forKey:@"type"];
        [dic setObject:ouname forKey:@"ouname"];
        [dic setObject:title forKey:@"title"];
        [dataArray addObject:dic];
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return dataArray;
}

+(NSMutableDictionary *)getUserDetailByUserguid:(NSString *)_userguid
{
    sqlite3 *framedb = [EpointFrameDB getFrameDB];
    
    NSString *sql = [NSString stringWithFormat:@"select displayname ,sex,ou.ouname,title,mobile,telephoneoffice,telephonehome,email,user.ouguid from user ,ou where  LOWER(user.ouguid) = LOWER(ou.ouguid) and userguid = '%@'",_userguid];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [sql UTF8String], -1, &statement, nil);
    NSMutableDictionary *user = [[NSMutableDictionary alloc]init];
    
    while (sqlite3_step(statement)==SQLITE_ROW)
    {
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)] forKey:@"displayname"];
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] forKey:@"sex"];
        
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)] forKey:@"ouname"];
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)] forKey:@"title"];
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)] forKey:@"mobile"];
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)] forKey:@"teleoffice"];
        
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)] forKey:@"telehome"];
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)] forKey:@"mail"];
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)] forKey:@"ouguid"];
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return user;
}

+(NSMutableArray *)getWebInfoCategoryByParent:(NSString *)parentOuGuid
{
    sqlite3 *framedb = [EpointFrameDB getFrameDB];
    
    NSString *selectSql=[NSString stringWithFormat:@"select * from webinfo_category where ParentCategoryGuid = '%@'",parentOuGuid];
    
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    
    NSMutableArray *dataArray = [[NSMutableArray alloc]init];
    while (sqlite3_step(statement)==SQLITE_ROW) {
        EpointCateGoryInfo *m = [[EpointCateGoryInfo alloc]init];
        m.CategoryName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        m.CategoryGuid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        m.ParentCategoryGuid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        m.IsTongzhiGongGao = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        [dataArray addObject:m];
        
    }
    
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return dataArray;
}

+(NSMutableArray *)getWebInfoCategoryByCategoryguid:(NSString *)categoryguid
{
    sqlite3 *framedb = [EpointFrameDB getFrameDB];
    
    NSString *selectSql=[NSString stringWithFormat:@"select * from webinfo_category where categoryguid = '%@'",categoryguid];
    
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    
    NSMutableArray *dataArray = [[NSMutableArray alloc]init];
    while (sqlite3_step(statement)==SQLITE_ROW) {
        EpointCateGoryInfo *m = [[EpointCateGoryInfo alloc]init];
        m.CategoryName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        m.CategoryGuid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        m.ParentCategoryGuid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        m.IsTongzhiGongGao = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        [dataArray addObject:m];
    }
    
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return dataArray;
}

+(NSMutableArray *)getAllUser
{
    sqlite3 *framedb = [EpointFrameDB getFrameDB];
    
    NSString *selectSql=@"select displayname ,userguid ,mobile,namepyshou from user";
    
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    
    NSMutableArray *dataArray = [[NSMutableArray alloc]init];
    while (sqlite3_step(statement)==SQLITE_ROW) {
        ContactModel *m = [[ContactModel alloc]init];
        m.displayname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        m.userguid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        m.mobile = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        m.namepyshou = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        [dataArray addObject:m];
    }
    
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return dataArray;
}

+(void)setConfigValue:(NSString *)key value:(NSString *)value
{
    sqlite3 *database = [EpointFrameDB getFrameDB];
    
    //删除
    NSString *deleteSql = [NSString stringWithFormat:@"delete from frame_config where key = ? "];
    sqlite3_stmt *deleteStatement;
    if (sqlite3_prepare_v2(database, [deleteSql UTF8String], -1, &deleteStatement, nil) == SQLITE_OK)
    {
        //or use [key cStringUsingEncoding:NSUTF8StringEncoding]
        sqlite3_bind_text(deleteStatement, 1, [key UTF8String], -1, NULL);
    }
    
    [DBFrameService sqlite3_step:deleteStatement database:database];
    
    //插入
    NSString *msInsertSql = [NSString stringWithFormat:@"insert into frame_config values(?,?)"];
    sqlite3_stmt *insertStatement;
    if (sqlite3_prepare_v2(database, [msInsertSql UTF8String], -1, &insertStatement, nil) == SQLITE_OK)
    {
        sqlite3_bind_text(insertStatement, 1, [key UTF8String], -1, NULL);
        sqlite3_bind_text(insertStatement, 2, [value UTF8String], -1, NULL);
    }
    
    [DBFrameService sqlite3_step:insertStatement database:database];
}

+(void)sqlite3_step:(sqlite3_stmt *)stmt database:(sqlite3 *)database
{
    //打印SQL语句
    //NSLog(@"sql: %s", sqlite3_sql(stmt));
    if(sqlite3_step(stmt) != SQLITE_DONE)
    {
        NSLog(@"Frame_DB_Error: %s", sqlite3_errmsg(database));
    }
    sqlite3_finalize(stmt);
}

+(NSString *)getConfigValue:(NSString *)key
{
    sqlite3 *database = [EpointFrameDB getFrameDB];
    NSString *selectSql=[NSString stringWithFormat:@"select value from frame_config where key = ?"];
    sqlite3_stmt *statement;
    NSString *nsvalue = @"";
    if (sqlite3_prepare_v2(database, [selectSql UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        sqlite3_bind_text(statement, 1, [key cStringUsingEncoding:NSUTF8StringEncoding], -1, NULL);
    }
    while (sqlite3_step(statement)==SQLITE_ROW) {
        char *value=(char *)sqlite3_column_text(statement, 0);
        nsvalue = [NSString stringWithUTF8String:value];
    }
    sqlite3_finalize(statement);
    //sqlite3_close(database);
    return nsvalue;
}

+(NSString *)getCacheValue:(NSString *)key
{
    EpointCacheDB *db = [[EpointCacheDB alloc]init];
    NSString *selectSql=[NSString stringWithFormat:@"select value from framecache where key = '%@'",key];
    sqlite3_stmt *statement;
    NSString *nsvalue = @"";
    sqlite3_prepare_v2(db.getCacheDB, [selectSql UTF8String], -1, &statement, nil);
    while (sqlite3_step(statement)==SQLITE_ROW) {
        char *value=(char *)sqlite3_column_text(statement, 0);
        nsvalue = [NSString stringWithUTF8String:value];
    }
    sqlite3_finalize(statement);
    return nsvalue;
}

+(NSString *)getSingErrorLog
{
    EpointCacheDB *db = [[EpointCacheDB alloc]init];
    NSString *selectSql=[NSString stringWithFormat:@"select value from framecache where key like 'ERROR%%'"];
    sqlite3_stmt *statement;
    NSString *nsvalue = @"";
    sqlite3_prepare_v2(db.getCacheDB, [selectSql UTF8String], -1, &statement, nil);
    while (sqlite3_step(statement)==SQLITE_ROW) {
        char *value=(char *)sqlite3_column_text(statement, 0);
        nsvalue = [NSString stringWithUTF8String:value];
    }
    char *errorMsg;

    sqlite3_exec(db.getCacheDB,[@"delete from framecache where key like 'ERROR%%'" UTF8String],NULL,NULL,&errorMsg);
    if (errorMsg!=NULL) {
        NSLog(@"ERR:%s",errorMsg);
    }
    sqlite3_finalize(statement);
    

    return nsvalue;
}

+(void)setCacheValue:(NSString *)key value:(NSString *)value
{
    value = [value stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
    EpointCacheDB *db = [[EpointCacheDB alloc]init];
    char *errorMsg;
    NSString *delete = [NSString stringWithFormat:@"delete from framecache where key = '%@' ",key];
    NSString *msInsertSql = [NSString stringWithFormat:@"insert into framecache values('%@','%@')",key,value];
    sqlite3_exec(db.getCacheDB,[delete UTF8String],NULL,NULL,&errorMsg);
    sqlite3_exec(db.getCacheDB, [msInsertSql UTF8String], NULL, NULL, &errorMsg);
    if (errorMsg!=NULL) {
        NSLog(@"ERR:%s",errorMsg);
    }
}

+(NSString *)getUserGuid
{
    return (NSString *)[self getUserDefaults:Epoint_User_UserGuid];
}

+(NSString *)getUserPhontNumber {
    NSString *userGuid = [self getUserGuid];
    
    sqlite3 *framedb = [EpointFrameDB getFrameDB];
    NSString *selectSql=[NSString stringWithFormat:@"select mobile from user where userguid = '%@'",userGuid];
    sqlite3_stmt *statement;
    NSString *nsvalue = @"";
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    while (sqlite3_step(statement)==SQLITE_ROW) {
        char *value=(char *)sqlite3_column_text(statement, 0);
        nsvalue = [NSString stringWithUTF8String:value];
    }
    sqlite3_finalize(statement);
    return nsvalue;
}

#pragma mark 根据LoginId获取Userguid
+(NSString *)getUserGuidByLoginId:(NSString *)loginid {
    
    sqlite3 *framedb = [EpointFrameDB getFrameDB];
    NSString *selectSql=[NSString stringWithFormat:@"select userguid from user where loginid = '%@'",loginid];
    sqlite3_stmt *statement;
    NSString *nsvalue = @"";
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    while (sqlite3_step(statement)==SQLITE_ROW) {
        char *value=(char *)sqlite3_column_text(statement, 0);
        nsvalue = [NSString stringWithUTF8String:value];
    }
    sqlite3_finalize(statement);
    return nsvalue;
}



+ (NSString *)getUserOuguid {
    NSString *userGuid = [self getUserGuid];
    
    sqlite3 *framedb = [EpointFrameDB getFrameDB];
    NSString *selectSql=[NSString stringWithFormat:@"select ouguid from user where userguid = '%@'",userGuid];
    sqlite3_stmt *statement;
    NSString *nsvalue = @"";
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    while (sqlite3_step(statement)==SQLITE_ROW) {
        char *value=(char *)sqlite3_column_text(statement, 0);
        nsvalue = [NSString stringWithUTF8String:value];
    }
    sqlite3_finalize(statement);
    return nsvalue;
}

+(void)setUserDefaults:(NSObject*)obj key:(NSString *)key
{
    NSUserDefaults *userDefaults = [NSUserDefaults standardUserDefaults];
    [userDefaults setObject:obj forKey:key];
    [userDefaults synchronize];
}

+(NSObject *)getUserDefaults:(NSString *)key
{
    NSUserDefaults *userDefaults = [NSUserDefaults standardUserDefaults];
    return [userDefaults objectForKey:key];
}

+(NSString *)getSecurityValue:(NSString *)key
{
    NSData *nsvalue = [DBFrameService getConfigBlob:key];
    
    NSData *data = [EncryptUtil DESDecrypt:nsvalue WithKey:key];
    
    NSString *str = [[NSString alloc]initWithData:data encoding:NSUTF8StringEncoding];
    
    return str;
}

+(void)setSecurityKey:(NSString *)key Value:(NSString *)value
{
    NSData *data = [EncryptUtil DESEncrypt:[value dataUsingEncoding:NSUTF8StringEncoding] WithKey:key];
    [DBFrameService setConfigBlob:key Value:data];
}

+(NSData *)getConfigBlob:(NSString *)key
{
    sqlite3 *database = [EpointFrameDB getFrameDB];
    NSString *selectSql=[NSString stringWithFormat:@"select value from frame_res where key = ?"];
    sqlite3_stmt *statement;
    NSData *nsvalue;
    if (sqlite3_prepare_v2(database, [selectSql UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        sqlite3_bind_text(statement, 1, [key cStringUsingEncoding:NSUTF8StringEncoding], -1, NULL);
    }
    while (sqlite3_step(statement)==SQLITE_ROW) {
        const void * bts = sqlite3_column_blob(statement, 0);
        //获取字段长度
        int size = sqlite3_column_bytes(statement, 0);
        if (bts!=NULL) {
            nsvalue = [[NSData alloc]initWithBytes:bts length:size];
        }
    }
    sqlite3_finalize(statement);
    return nsvalue;
}

+(void)setConfigBlob:(NSString *)key Value:(NSData *)value
{
    sqlite3 *database = [EpointFrameDB getFrameDB];
    
    //删除
    NSString *deleteSql = [NSString stringWithFormat:@"delete from frame_res where key = ? "];
    sqlite3_stmt *deleteStatement;
    if (sqlite3_prepare_v2(database, [deleteSql UTF8String], -1, &deleteStatement, nil) == SQLITE_OK)
    {
        //or use [key cStringUsingEncoding:NSUTF8StringEncoding]
        sqlite3_bind_text(deleteStatement, 1, [key UTF8String], -1, NULL);
    }
    
    [DBFrameService sqlite3_step:deleteStatement database:database];
    
    //插入
    NSString *msInsertSql = [NSString stringWithFormat:@"insert into frame_res values(?,?)"];
    sqlite3_stmt *insertStatement;
    if (sqlite3_prepare_v2(database, [msInsertSql UTF8String], -1, &insertStatement, nil) == SQLITE_OK)
    {
        sqlite3_bind_text(insertStatement, 1, [key UTF8String], -1, NULL);
        sqlite3_bind_blob(insertStatement, 2, [value bytes], [value length], NULL);
    }
    
    [DBFrameService sqlite3_step:insertStatement database:database];
}

+(void)deleteFrameCache
{
    EpointCacheDB *db = [[EpointCacheDB alloc]init];
    char *errorMsg;
    NSString *delete = [NSString stringWithFormat:@"delete from framecache"];
    sqlite3_exec(db.getCacheDB,[delete UTF8String],NULL,NULL,&errorMsg);
    sqlite3_close(db.getCacheDB);
    if (errorMsg!=NULL) {
        NSLog(@"ERR:%s",errorMsg);
    }
}
@end
